package merlin.dao;

import java.util.*;
import java.util.Date;
import java.sql.*;

import merlin.beans.*;

/**
 *  DAO to interact with the NSD.WEBSITE_ORDERS table that stores the address and credit card details
 *  of the customers who place orders on the site.
 *
 * 	Changes
 * 	-------
 * 				Created.
 * 	09 Oct,2006 Added code to handle the CVV field.
 */

public class WebsiteOrdersDAO implements java.io.Serializable{

	Connection con;

	public WebsiteOrdersDAO(Connection connection){
		con = connection;
	}

	public int addWebsiteOrder(WebsiteOrdersBean bean)throws SQLException{

		Statement stmt = con.createStatement();
   		ResultSet rs = stmt.executeQuery("SELECT MAX(COALESCE(WEBSITE_ORDERS_ID,0)) + 1 FROM NSD.WEBSITE_ORDERS");
		rs.next();
		int newID = rs.getInt(1);
		System.out.println(newID);
		rs.close();
		stmt.close();

		String sql = "INSERT INTO NSD.WEBSITE_ORDERS(" +
						"WEBSITE_ORDERS_ID,DD_SALUTATION,DD_FIRST_NAME,DD_SURNAME,DD_POSITION," +
						"DD_COMPANY_NAME,DD_ADDRESS_LINE1,DD_ADDRESS_LINE2,DD_TOWN,DD_COUNTY," +
						"DD_POSTCODE,TELEPHONE,EMAIL_ADDRESS,CREDIT_CARD_NUMBER,CREDIT_CARD_AUTHORISATION_CODE,EXPIRY_DATE," +
						"CC_SALUTATION,CC_FIRST_NAME,CC_SURNAME," +
						"CC_POSITION,CC_COMPANY_NAME,CC_ADDRESS_LINE1,CC_ADDRESS_LINE2,CC_TOWN," +
						"CC_COUNTY,CC_POSTCODE,CD_DATABASE_REQUESTED_INDICATOR,BOOK_REQUESTED_INDICATOR,WO_PROMOTIONAL_CODE," +
						"EBOOK_REQUESTED_INDICATOR, WO_CUSTOMER_DETAILS_ID, WO_ORDER_DATE) VALUES(" +
						"?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + bean.getCdDatabaseRequestedIndicator()
						+ "," + bean.getBookRequestedIndicator() + ",?," +  bean.isEBookRequestedIndicator() + ",?, CURRENT_DATE)";

		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1,newID);
		pstmt.setString(2,bean.getDdSalutation());
		pstmt.setString(3,bean.getDdFirstName());
		pstmt.setString(4,bean.getDdSurname());
		pstmt.setString(5,bean.getDdPosition());
		pstmt.setString(6,bean.getDdCompanyName());
		pstmt.setString(7,bean.getDdAddressLine1());
		pstmt.setString(8,bean.getDdAddressLine2());
		pstmt.setString(9,bean.getDdTown());
		pstmt.setString(10,bean.getDdCounty());
		pstmt.setString(11,bean.getDdPostcode());
		pstmt.setString(12,bean.getTelephone());
		pstmt.setString(13,bean.getEmailAddress());
		pstmt.setString(14,bean.getCreditCardNumber());
		pstmt.setString(15,bean.getCardVerificationValue());
		pstmt.setString(16,bean.getExpiryDate());
		//pstmt.setBoolean(16,bean.getCdDatabaseRequestedIndicator());
		pstmt.setString(17,bean.getCcSalutation());
		pstmt.setString(18,bean.getCcFirstName());
		pstmt.setString(19,bean.getCcSurname());
		pstmt.setString(20,bean.getCcPosition());
		pstmt.setString(21,bean.getCcCompanyName());
		pstmt.setString(22,bean.getCcAddressLine1());
		pstmt.setString(23,bean.getCcAddressLine2());
		pstmt.setString(24,bean.getCcTown());
		pstmt.setString(25,bean.getCcCounty());
		pstmt.setString(26,bean.getCcPostcode());
		//pstmt.setBoolean(27,bean.getBookRequestedIndicator());
		pstmt.setString(27,bean.getDiscountCode());
		if(bean.getCustomerDetailsId() != null){
			pstmt.setInt(28, bean.getCustomerDetailsId());
		}else{
			pstmt.setNull(28, Types.INTEGER);
		}
		int updateCols = pstmt.executeUpdate();
		pstmt.close();
		System.out.println("Update count " + updateCols);
		System.out.println("new id " + newID);
		return newID;
	}

	public int updateWebsiteOrder(int orderId,String vpsTxID,String secKey)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("UPDATE WEBSITE_ORDERS SET WO_VPSTX_ID = ? ,WO_SECURITY_KEY = ? WHERE WEBSITE_ORDERS_ID = ?");
		pstmt.setString(1,vpsTxID);
		pstmt.setString(2,secKey);
		pstmt.setInt(3,orderId);
		int updateCols = pstmt.executeUpdate();
		pstmt.close();
		return updateCols;
	}

	public int save3DSecureMDValue(int orderId,String mdValue)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("UPDATE WEBSITE_ORDERS SET WO_MD_VALUE = ? WHERE WEBSITE_ORDERS_ID = ?");
		pstmt.setString(1,mdValue);
		pstmt.setInt(2,orderId);
		int updateCols = pstmt.executeUpdate();
		pstmt.close();
		return updateCols;
	}

	public WebsiteOrdersBean selectWebsiteOrdersByPK(String emailAddress)throws SQLException{

		PreparedStatement pstmt = con.prepareStatement("SELECT WEBSITE_ORDERS_ID,DD_SALUTATION,DD_FIRST_NAME,DD_SURNAME,DD_POSITION,DD_COMPANY_NAME,DD_ADDRESS_LINE1,DD_ADDRESS_LINE2,DD_TOWN,DD_COUNTY,DD_POSTCODE,TELEPHONE,EMAIL_ADDRESS,CREDIT_CARD_NUMBER,EXPIRY_DATE,CD_DATABASE_REQUESTED_INDICATOR,CC_SALUTATION,CC_FIRST_NAME,CC_SURNAME,CC_POSITION,CC_COMPANY_NAME,CC_ADDRESS_LINE1,CC_ADDRESS_LINE2,CC_TOWN,CC_COUNTY,CC_POSTCODE,BOOK_REQUESTED_INDICATOR,EBOOK_REQUESTED_INDICATOR FROM WEBSITE_ORDERS,IW_CUSTOMER_DETAILS WHERE WEBSITE_ORDERS_ID = IW_WEBSITE_ORDERS_ID AND IW_EMAIL_ADDRESS = ? ");
		pstmt.setString(1,emailAddress);
		ResultSet rs = pstmt.executeQuery();
		WebsiteOrdersBean bn = null;
		if(rs.next()){
			bn = new WebsiteOrdersBean();
			bn.setWebsiteOrdersId(rs.getInt("WEBSITE_ORDERS_ID"));
			bn.setDdSalutation(rs.getString("DD_SALUTATION"));
			bn.setDdFirstName(rs.getString("DD_FIRST_NAME"));
			bn.setDdSurname(rs.getString("DD_SURNAME"));
			bn.setDdPosition(rs.getString("DD_POSITION"));
			bn.setDdCompanyName(rs.getString("DD_COMPANY_NAME"));
			bn.setDdAddressLine1(rs.getString("DD_ADDRESS_LINE1"));
			bn.setDdAddressLine2(rs.getString("DD_ADDRESS_LINE2"));
			bn.setDdTown(rs.getString("DD_TOWN"));
			bn.setDdCounty(rs.getString("DD_COUNTY"));
			bn.setDdPostcode(rs.getString("DD_POSTCODE"));
			bn.setTelephone(rs.getString("TELEPHONE"));
			bn.setEmailAddress(rs.getString("EMAIL_ADDRESS"));
			bn.setCreditCardNumber(rs.getString("CREDIT_CARD_NUMBER"));
			bn.setExpiryDate(rs.getString("EXPIRY_DATE"));
			bn.setCdDatabaseRequestedIndicator(rs.getBoolean("CD_DATABASE_REQUESTED_INDICATOR"));
			bn.setCcSalutation(rs.getString("CC_SALUTATION"));
			bn.setCcFirstName(rs.getString("CC_FIRST_NAME"));
			bn.setCcSurname(rs.getString("CC_SURNAME"));
			bn.setCcPosition(rs.getString("CC_POSITION"));
			bn.setCcCompanyName(rs.getString("CC_COMPANY_NAME"));
			bn.setCcAddressLine1(rs.getString("CC_ADDRESS_LINE1"));
			bn.setCcAddressLine2(rs.getString("CC_ADDRESS_LINE2"));
			bn.setCcTown(rs.getString("CC_TOWN"));
			bn.setCcCounty(rs.getString("CC_COUNTY"));
			bn.setCcPostcode(rs.getString("CC_POSTCODE"));
			bn.setBookRequestedIndicator(rs.getBoolean("BOOK_REQUESTED_INDICATOR"));
			bn.setEBookRequestedIndicator(rs.getBoolean("EBOOK_REQUESTED_INDICATOR"));
		}
		rs.close();
		pstmt.close();
		return bn;
	}

	public int deleteWebsiteOrders(int orderId)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("DELETE FROM WEBSITE_ORDERS WHERE WEBSITE_ORDERS_ID = ?");
		pstmt.setInt(1,orderId);
		int updateCols = pstmt.executeUpdate();
		pstmt.close();
		return updateCols;
	}
	
	public Date getWebsiteOrderDateByOrderId(int orderId)throws SQLException{
		Date orderDate = null;
		
		PreparedStatement pstmt = con.prepareStatement("SELECT WO_ORDER_DATE FROM WEBSITE_ORDERS WHERE WEBSITE_ORDERS_ID = ?");
		pstmt.setInt(1,orderId);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			orderDate = rs.getDate(1);
			if(orderDate == null){
				// some old orders don't have the order date. So these are valid orders.
				orderDate = new Date();
			}
		}
		rs.close();
		pstmt.close();
		
		return orderDate;
	}
/*
	public DbResultSet selectWebsiteOrdersRecords(String whereClause,String orderByClause)throws SQLException{

		ArrayList records = new ArrayList();
		Connection con = getConnection();
		String sql ="SELECT WEBSITE_ORDERS_ID,DD_SALUTATION,DD_FIRST_NAME,DD_SURNAME,DD_POSITION,DD_COMPANY_NAME,DD_ADDRESS_LINE1,DD_ADDRESS_LINE2,DD_TOWN,DD_COUNTY,DD_POSTCODE,TELEPHONE,EMAIL_ADDRESS,CREDIT_CARD_NUMBER,EXPIRY_DATE,CD_DATABASE_REQUESTED_INDICATOR,CC_SALUTATION,CC_FIRST_NAME,CC_SURNAME,CC_POSITION,CC_COMPANY_NAME,CC_ADDRESS_LINE1,CC_ADDRESS_LINE2,CC_TOWN,CC_COUNTY,CC_POSTCODE,BOOK_REQUESTED_INDICATOR FROM WEBSITE_ORDERS";
		if(whereClause !=null && !whereClause.equals(""))sql += " WHERE " + whereClause;
		if(orderByClause !=null && !orderByClause.equals(""))sql += " ORDER BY " + orderByClause;
		Statement stmt =  con.createStatement();
		ResultSet rs =  stmt.executeQuery(sql);
		while(rs.next()){
			WebsiteOrdersBean bn = new WebsiteOrdersBean(rs.getInt("WEBSITE_ORDERS_ID"),rs.getString("DD_SALUTATION"),rs.getString("DD_FIRST_NAME"),rs.getString("DD_SURNAME"),rs.getString("DD_POSITION"),rs.getString("DD_COMPANY_NAME"),rs.getString("DD_ADDRESS_LINE1"),rs.getString("DD_ADDRESS_LINE2"),rs.getString("DD_TOWN"),rs.getString("DD_COUNTY"),rs.getString("DD_POSTCODE"),rs.getString("TELEPHONE"),rs.getString("EMAIL_ADDRESS"),rs.getString("CREDIT_CARD_NUMBER"),rs.getString("EXPIRY_DATE"),rs.getBoolean("CD_DATABASE_REQUESTED_INDICATOR"),rs.getString("CC_SALUTATION"),rs.getString("CC_FIRST_NAME"),rs.getString("CC_SURNAME"),rs.getString("CC_POSITION"),rs.getString("CC_COMPANY_NAME"),rs.getString("CC_ADDRESS_LINE1"),rs.getString("CC_ADDRESS_LINE2"),rs.getString("CC_TOWN"),rs.getString("CC_COUNTY"),rs.getString("CC_POSTCODE"),rs.getBoolean("BOOK_REQUESTED_INDICATOR"));
			records.add(bn);
		}
		rs.close();
		stmt.close();
		returnConnection(con);
		if(!records.isEmpty())return new DbResultSet(records,records.size()); else return new DbResultSet();
	}



	public int updateWebsiteOrders(WebsiteOrdersBean bean)throws SQLException{

		Connection con = getConnection();
		con.setAutoCommit(false);
		PreparedStatement pstmt = con.prepareStatement("UPDATE WEBSITE_ORDERS SET DD_SALUTATION = ? ,DD_FIRST_NAME = ? ,DD_SURNAME = ? ,DD_POSITION = ? ,DD_COMPANY_NAME = ? ,DD_ADDRESS_LINE1 = ? ,DD_ADDRESS_LINE2 = ? ,DD_TOWN = ? ,DD_COUNTY = ? ,DD_POSTCODE = ? ,TELEPHONE = ? ,EMAIL_ADDRESS = ? ,CREDIT_CARD_NUMBER = ? ,EXPIRY_DATE = ? ,CD_DATABASE_REQUESTED_INDICATOR = ? ,CC_SALUTATION = ? ,CC_FIRST_NAME = ? ,CC_SURNAME = ? ,CC_POSITION = ? ,CC_COMPANY_NAME = ? ,CC_ADDRESS_LINE1 = ? ,CC_ADDRESS_LINE2 = ? ,CC_TOWN = ? ,CC_COUNTY = ? ,CC_POSTCODE = ? ,BOOK_REQUESTED_INDICATOR = ?  WHERE WEBSITE_ORDERS_ID = ?");
		pstmt.setString(1,bean.getDdSalutation());
		pstmt.setString(2,bean.getDdFirstName());
		pstmt.setString(3,bean.getDdSurname());
		pstmt.setString(4,bean.getDdPosition());
		pstmt.setString(5,bean.getDdCompanyName());
		pstmt.setString(6,bean.getDdAddressLine1());
		pstmt.setString(7,bean.getDdAddressLine2());
		pstmt.setString(8,bean.getDdTown());
		pstmt.setString(9,bean.getDdCounty());
		pstmt.setString(10,bean.getDdPostcode());
		pstmt.setString(11,bean.getTelephone());
		pstmt.setString(12,bean.getEmailAddress());
		pstmt.setString(13,bean.getCreditCardNumber());
		pstmt.setString(14,bean.getExpiryDate());
		pstmt.setBoolean(15,bean.getCdDatabaseRequestedIndicator());
		pstmt.setString(16,bean.getCcSalutation());
		pstmt.setString(17,bean.getCcFirstName());
		pstmt.setString(18,bean.getCcSurname());
		pstmt.setString(19,bean.getCcPosition());
		pstmt.setString(20,bean.getCcCompanyName());
		pstmt.setString(21,bean.getCcAddressLine1());
		pstmt.setString(22,bean.getCcAddressLine2());
		pstmt.setString(23,bean.getCcTown());
		pstmt.setString(24,bean.getCcCounty());
		pstmt.setString(25,bean.getCcPostcode());
		pstmt.setBoolean(26,bean.getBookRequestedIndicator());
		pstmt.setInt(27,bean.getWebsiteOrdersId());
		int updateCols = pstmt.executeUpdate();
		con.commit();
		pstmt.close();
		returnConnection(con);
		return updateCols;
	}

	public int deleteWebsiteOrders(WebsiteOrdersBean bean)throws SQLException{

		Connection con = getConnection();
		con.setAutoCommit(false);
		PreparedStatement pstmt = con.prepareStatement("DELETE FROM WEBSITE_ORDERS WHERE WEBSITE_ORDERS_ID = ?");
		pstmt.setInt(1,bean.getWebsiteOrdersId());
		int updateCols = pstmt.executeUpdate();
		con.commit();
		pstmt.close();
		returnConnection(con);
		return updateCols;
	}
	*/
}